package com.mc.dao.Impl;

import com.mc.bean.Student;
import com.mc.dao.StudentDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zl
 * 学生数据访问实现类
 */
public class StudentDaoImpl extends BaseDao implements StudentDao {

    @Override
    public List<Student> findAll() {
        List<Student> list = new ArrayList<>();
        //加载驱动
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select * from student";
            super.getPst(sql);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //创建对象
                Student student = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
                        rs.getInt(5), rs.getString(6), rs.getString(7), rs.getInt(8), rs.getInt(9),
                        rs.getInt(10));
                //放在集合中
                list.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return list;
    }

    @Override
    public Student findBySid(int sid) {
        Student student = null;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select * from student where sid = ?";
            super.getPst(sql);
            pstmt.setInt(1, sid);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()) {
                student = new Student(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getInt(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getInt(8),
                        rs.getInt(9),
                        rs.getInt(10));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return student;
    }

    @Override
    public Student findByTelPass(String tel, String pass) {
        Student student = null;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select * from student where stel=? and spassword=?";
            super.getPst(sql);
            pstmt.setString(1, tel);
            pstmt.setString(2, pass);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()) {
                student = new Student(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getInt(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getInt(8),
                        rs.getInt(9),
                        rs.getInt(10));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return student;
    }

    @Override
    public List<Student> findByColId(int colid) {
        List<Student> list = new ArrayList<>();
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select * from student where colid=?";
            super.getPst(sql);
            pstmt.setInt(1, colid);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Student student = new Student(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getInt(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getInt(8),
                        rs.getInt(9),
                        rs.getInt(10));

                //放在集合中
                list.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return list;
    }

    @Override
    public List<Student> findByGid(int gid) {
        List<Student> list = null;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select * from student where gid=?";
            super.getPst(sql);
            pstmt.setInt(1, gid);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Student student = new Student(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getInt(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getInt(8),
                        rs.getInt(9),
                        rs.getInt(10));

                //放在集合中
                list.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return list;
    }

    @Override
    public int add(Student student) {
        int i = 0;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "insert into student(sid,sname,stel,spassword,sage,ssex,sheadurl,colid,gid,rid) value(?,?,?,?,?,?,?,?,?,?)";
            super.getPst(sql);
            pstmt.setInt(1,student.getSid());
            pstmt.setString(2,student.getSname());
            pstmt.setString(3,student.getStel());
            pstmt.setString(4,student.getSpassword());
            pstmt.setInt(5,student.getSage());
            pstmt.setString(6,student.getSsex());
            pstmt.setString(7,student.getSheadurl());
            pstmt.setInt(8,student.getColid());
            pstmt.setInt(9,student.getGid());
            pstmt.setInt(10,student.getRid());
            //4执行预编译指令+得到结果集
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return i;
    }

    @Override
    public int delete(int sid) {
        int i = 0;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "delete from student where sid = ?";
            super.getPst(sql);
            pstmt.setInt(1, sid);
            i = pstmt.executeUpdate();
            //4执行预编译指令+得到结果集

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return i;
    }

    @Override
    public int update(Student student) {
        int i = 0;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "UPDATE student SET sname=?,stel=?,spassword=?,sage=?,ssex=?,sheadurl=?,colid=?,gid=?,rid=? WHERE sid=?";
            super.getPst(sql);

            pstmt.setString(1,student.getSname());
            pstmt.setString(2,student.getStel());
            pstmt.setString(3,student.getSpassword());
            pstmt.setInt(4,student.getSage());
            pstmt.setString(5,student.getSsex());
            pstmt.setString(6,student.getSheadurl());
            pstmt.setInt(7,student.getColid());
            pstmt.setInt(8,student.getGid());
            pstmt.setInt(9,student.getRid());
            pstmt.setInt(10,student.getSid());
            i = pstmt.executeUpdate();
            //4执行预编译指令+得到结果集
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return i;
    }

    @Override
    public Student findByStel(String stel) {
        Student student = null;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select * from student where stel = ?";
            super.getPst(sql);
            pstmt.setString(1, stel);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()) {
                student = new Student(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getInt(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getInt(8),
                        rs.getInt(9),
                        rs.getInt(10));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return student;
    }
}
